#!/opt/lampp/bin/perl 

use DBI;
use CGI;

my $user_name = "traker";
my $user_password = "mckenna";
my $sql_server = "localhost";
my $db_name = "ttraker";
my $back_loc = "/home/alan/Dropbox/Boxxy";

my $dbh;
initialize_dbi();

my $log = "\/opt\/lampp\/cgi-bin\/logs\/log.txt";
my $start = get_yesterday();

print "LOOKING AT $start\n";

print "update_job_event_elapsed_adj_all_loc ($start)\n";
update_job_event_elapsed_adj_all_loc ($start);

print "update_job_headers_time_cost_all_loc  ($start)\n";
update_job_headers_time_cost_all_loc ($start);

print "update_daily_from_payroll_list_all_loc  ($start)\n";
update_daily_from_payroll_list_all_loc  ($start);


# backup_dbase();

sub initialize_dbi
{
   $drh = DBI->install_driver( 'mysql' );
   $dbh = DBI->connect("DBI:mysql:$db_name:$sql_server", $user_name, $user_password);
   die "Cannot connect: $D: $DBI::errstr\n"
      unless $dbh;
}

sub run_statement_old
{
   my $stmt = "$_[0]";
   $sth = $dbh ->prepare($stmt);
   $sth->execute;
}

sub run_statement
{
   my $stmt = "$_[0]";
#   print "$stmt <br/>";
   $sth = $dbh ->prepare($stmt);
   $sth->execute;
   open (LOG, ">> $log")
		or die "CAN NOT OPEN $log";	
	print LOG "$stmt\n";
	close LOG;
}

sub get_yesterday
{

  my $stmnt = "SELECT subdate(current_date,1)";
   run_statement ($stmnt);
   my $now = $sth->fetchrow;
   $sth->finish;
   return $now;
}

sub update_job_event_elapsed_adj_all_loc 
{
	my $date = $_[0];
	my $stmnt = "UPDATE job_events AS JE 
	JOIN job_headers AS JH ON (JH.job_num = JE.job_num)  
	JOIN employees AS E ON (E.employee_num = JH.employee_num)
	JOIN payroll_hrs AS PH ON (PH.employee_num = JH.employee_num)
	SET JE.elapsed_adj = JE.elapsed*PH.time_adj 
	WHERE PH.date = JE.pay_date AND
	JE.pay_date = '$date'";
	run_statement ($stmnt);
	my $stmnt = "UPDATE job_events AS JE 
	JOIN job_headers AS JH ON (JH.job_num = JE.job_num)
	JOIN employees AS E ON (E.employee_num = JH.employee_num)  
	SET JE.lab_cost = JE.elapsed*JH.rate/60,
	JE.lab_cost_adj = JE.elapsed_adj*JH.rate/60
	WHERE JE.pay_date = '$date'";
	run_statement ($stmnt);
	$sth->finish;
}

sub update_job_headers_time_cost_all_loc
{
	my $date = $_[0];
	my @jobs = ();
	my $stmnt = "SELECT job_num FROM job_events WHERE pay_date = '$date' GROUP BY job_num";
	run_statement ($stmnt);
	while (($tmp) = $sth->fetchrow) {
		push @jobs, $tmp;
	}
	foreach my $job (@jobs) {
		$stmnt = "UPDATE job_headers SET 
				act_min = ( SELECT SUM(elapsed) FROM job_events WHERE  job_num = $job AND active = 'Y'),
				act_min_adj = ( SELECT SUM(elapsed_adj) FROM job_events WHERE  job_num = $job AND active = 'Y'),
				lab_cost_adj = ( SELECT SUM(lab_cost_adj) FROM job_events WHERE  job_num = $job AND active = 'Y'),
				std_min = ( SELECT SUM(std_time) FROM task_events WHERE job_num = $job AND active = 'Y'),
				revenue = ( SELECT SUM(revenue) FROM task_events WHERE job_num = $job AND active = 'Y')
				WHERE job_num = $job";
		run_statement ($stmnt);
#		print "$stmnt<br/>";
	}
	$sth->finish;
}

sub update_daily_from_payroll_list_all_loc 
{
	my $date = $_[0];
	my @employees=();
	my $stmnt = "SELECT employee_num FROM payroll_hrs WHERE date = '$date' GROUP BY employee_num";
	run_statement ($stmnt);
	while (($tmp) = $sth->fetchrow) {
		push @employees, $tmp;
	}
	foreach my $emp (@employees) {
		update_daily_time_stats ($emp, $date)
	}
}

sub update_daily_time_stats
{
	my $num = $_[0];
	my $date = $_[1];
	my $stmnt;
	if ( $date eq "") {
		$stmnt = "SELECT DATE(start) FROM logged_in WHERE employee_num = $num";
		run_statement ($stmnt);
		$date = $sth->fetchrow; 
		$sth->finish;
	}	
	$stmnt = "SELECT SUM(JE.elapsed) FROM job_events AS JE 
				JOIN job_headers AS JH ON (JH.job_num = JE.job_num) 
				JOIN clients AS C ON (C.client_id = JH.client_id) 
				WHERE pay_date = '$date' AND JH.employee_num = $num AND C.category > 0";
	run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	$tot_min = $rs->[0]->[0];
	if ($tot_min eq "" ) { 
		$tot_min = 0;
	}	
	$stmnt = "SELECT SUM(JE.elapsed) FROM job_events AS JE 
				JOIN job_headers AS JH ON (JH.job_num = JE.job_num) 
				JOIN clients AS C ON (C.client_id = JH.client_id) 
				WHERE	pay_date = '$date' AND JH.employee_num = $num AND C.category = 10";
	run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	$cli_min = $rs->[0]->[0];
	if ($cli_min eq "" ) { 
		$cli_min = 0;
	}	
	$stmnt = "SELECT SUM(JE.elapsed) FROM job_events AS JE 
				JOIN job_headers AS JH ON (JH.job_num = JE.job_num)
				JOIN clients AS C ON (C.client_id = JH.client_id) 
				WHERE pay_date = '$date' AND JH.employee_num = $num 
				AND C.category = 8";
	run_statement ($stmnt);
#	print "$stmnt<br/>";
	$rs = $sth->fetchall_arrayref();
	$mck_min = $rs->[0]->[0];
	if ($mck_min eq "" ) { 
		$mck_min = 0;
	}	
	$stmnt = "UPDATE payroll_hrs SET tot_min = $tot_min, cli_min = $cli_min, mck_min=$mck_min, oth_min = (vac+sta+sic+brv)*60 
				WHERE date = '$date' and employee_num = $num";
	run_statement ($stmnt);
	$sth->finish;
}





sub backup_dbase 
{
	my $stmnt = "SELECT DATE_FORMAT(CURDATE(), '%Y_%b_%d_TT.sql')";
	run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	my $name = $rs->[0]->[0];
	$sth->finish;
#	print "mysqldump -p$user_password -u $user_name $db_name > ${back_loc}/${name}\n";	
	system ("mysqldump -p$user_password -u $user_name $db_name > ${back_loc}/${name}");
	return $name;
}



